using OrderSystem.Common;
using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
	public class Flowcards : IDisposable
	{
		private SqlDataAdapter dataAdapter;

		private SqlCommand selectCommand;

		private SqlCommand insertCommand;

		private SqlCommand updateCommand;

		private SqlCommand deleteCommand;

		public bool UpdateRemark(string sFlowcardCode, string sNewRemark)
		{
			string cmdText = "UPDATE [Flowcard] SET Remark=@Remrk WHERE Code=@FlowcardCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@FlowcardCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@Remrk", SqlDbType.NVarChar, 64));
				this.updateCommand.Parameters["@FlowcardCode"].Value = sFlowcardCode;
				this.updateCommand.Parameters["@Remrk"].Value = sNewRemark;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataSet GetTypeSubtotalOfFlowcard(long nFlowcardID)
		{
			string str = "SELECT pt.[Name] AS 类型,sum(fi.Quantity) AS 数量,sum(dbo.ItemSquare(p.Width,p.Height,fi.Quantity)) AS 面积,sum(dbo.ItemPerimeter(p.Width,p.Height,fi.Quantity)) AS 周长,p.Price as 价格 FROM FlowcardItem as fi inner join Product AS p on fi.ProductID=p.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE fi.FlowcardID=@FlowcardID group by pt.[Name],p.Price";
			string str2 = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*fi.Quantity) AS 数量,ts.unitPrice as 单价 FROM Technics as ts inner join Product AS p on ts.ProductID=p.PKID inner join FlowcardItem as fi on fi.ProductID=p.PKID WHERE fi.FlowcardID=@FlowcardID group by ts.technicsName,ts.unitPrice order by ts.technicsName";
			DataSet dataSet = new DataSet("TypeSubtotal");
			try
			{
				this.selectCommand = new SqlCommand(str + str2, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@FlowcardID", SqlDbType.BigInt));
				this.selectCommand.Parameters["@FlowcardID"].Value = nFlowcardID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public int GetNumberOfRows(long nFlowcardID)
		{
			string cmdText = "SELECT COUNT(*) FROM FlowcardItem WHERE FlowcardID=@FlowcardID";
			int result = 0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@FlowcardID", SqlDbType.BigInt));
				this.selectCommand.Parameters["@FlowcardID"].Value = nFlowcardID;
				string text = this.selectCommand.ExecuteScalar().ToString();
				if (text != "")
				{
					result = Convert.ToInt32(text);
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool LockFlowcard(string sOrderCode)
		{
			string cmdText = "UPDATE [Order] SET LockedFlowcard=1 WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UnLockFlowcard(string orderCode)
		{
			string cmdText = "UPDATE [Order] SET LockedFlowcard=0 WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = orderCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataTable LoadFlowcardByFlowcardCode(string sFlowcardCode)
		{
			string cmdText = "SELECT fi.PKID AS 流程卡序号,fi.Code AS 流程卡号,c.PKID as 客户序号,c.CompanyName AS 公司名称,c.Linkman AS 公司联系人,pj.PKID AS 计划序号,pj.ProjectName AS 计划名称,pj.Linkman AS 计划联系人,pj.LinkmanPhone AS 计划联系人电话,pj.ProjectAddress AS 计划地址,o.OrderDate AS 下单日期,o.SpeakForDate AS 订单日期,o.DeliveryDate AS 交货日期,o.Remark AS 订单备注,fi.Remark as 流程卡备注,o.LabelName AS 标签名称,op.PKID AS 操作者ID,op.[Name] AS 操作者,o.Salesperson AS 业务员 FROM Flowcard as fi inner join [Order] AS o on fi.OrderID=o.PKID INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON fi.OperatorID=op.PKID WHERE fi.Code=@FlowcardCode";
			DataTable dataTable = new DataTable("Flowcard");
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@FlowcardCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@FlowcardCode"].Value = sFlowcardCode;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public int GetPrintedQuantity(long nFlowcardID)
		{
			string cmdText = "SELECT PrintedQuantity from [Flowcard] where PKID=@FlowcardID";
			int result = 0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@FlowcardID", SqlDbType.BigInt));
				this.selectCommand.Parameters["@FlowcardID"].Value = nFlowcardID;
				object obj = this.selectCommand.ExecuteScalar();
				if (obj != null)
				{
					result = Convert.ToInt32(obj);
				}
				else
				{
					result = -1;
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool PrintOnce(long nFlowcardID)
		{
			string cmdText = "Update [Flowcard] set PrintedQuantity=PrintedQuantity+1 where PKID=@FlowcardID";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@FlowcardID", SqlDbType.BigInt));
				this.updateCommand.Parameters["@FlowcardID"].Value = nFlowcardID;
				this.updateCommand.ExecuteScalar();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public int GetPrintedQuantityForLabel(long nFlowcardID)
		{
			string cmdText = "SELECT LabelPrintedQuantity from [Flowcard] where PKID=@FlowcardID";
			int result = 0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@FlowcardID", SqlDbType.BigInt));
				this.selectCommand.Parameters["@FlowcardID"].Value = nFlowcardID;
				object obj = this.selectCommand.ExecuteScalar();
				if (obj != null)
				{
					result = Convert.ToInt32(obj);
				}
				else
				{
					result = -1;
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool PrintOnceForLabel(long nFlowcardID)
		{
			string cmdText = "Update [Flowcard] set LabelPrintedQuantity=LabelPrintedQuantity+1 where PKID=@FlowcardID";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@FlowcardID", SqlDbType.BigInt));
				this.updateCommand.Parameters["@FlowcardID"].Value = nFlowcardID;
				this.updateCommand.ExecuteScalar();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataTable GetFlowcardsByCondition(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			DataTable dataTable = new DataTable("Order");
			string text = "SELECT fd.PKID AS 流程卡序号,fd.Code AS 流程卡号,c.CompanyName AS 客户,pj.ProjectName AS 计划,convert(char(10),o.SpeakForDate,120) AS 订购日期,o.Remark+'/'+fd.Remark AS 备注,op.Name AS 操作者,o.PrintedQuantity as 订单生成次数,o.LabelPrintedQuantity 标签打印次数,o.LabelName as 标签名称 FROM Flowcard as fd inner join [Order] AS o on fd.OrderID=o.PKID INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID";
			text = text + sqlCondition + " ORDER BY c.[CompanyName],o.PKID,fd.PKID";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool AutoInsertOrder(long nOrderID, int nOperatorID)
		{
			try
			{
				this.insertCommand = new SqlCommand("AutoCreateFlowcard", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.CommandType = CommandType.StoredProcedure;
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.insertCommand.Parameters["@OrderID"].Value = nOrderID;
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@OperatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Delete(long nOrderID)
		{
			string cmdText = "SET XACT_ABORT ON BEGIN TRANSACTION delete FlowcardItem from FlowcardItem as fi inner join Flowcard as fc on fi.FlowcardID=fc.PKID where fc.OrderID=@OrderID delete Flowcard where OrderID=@OrderID update [Order] set FlowcardSerialNumber=0 where PKID=@OrderID COMMIT TRANSACTION";
			try
			{
				this.deleteCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.deleteCommand.Connection.Open();
				this.deleteCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.deleteCommand.Parameters["@OrderID"].Value = nOrderID;
				this.deleteCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		private string GetSqlCondition(OrderCondition oc)
		{
			string text = "";
			if (oc.CustomerID > -1)
			{
				if (text == "")
				{
					text += "pj.CustomerID=@CustomerID";
				}
				else
				{
					text += " AND pj.CustomerID=@CustomerID";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
				this.selectCommand.Parameters["@CustomerID"].Value = oc.CustomerID;
			}
			if (oc.ProjectID > -1)
			{
				if (text == "")
				{
					text += "o.ProjectID=@ProjectID";
				}
				else
				{
					text += " AND o.ProjectID=@ProjectID";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int));
				this.selectCommand.Parameters["@ProjectID"].Value = oc.ProjectID;
			}
			if (oc.SpeakForDateStart != null)
			{
				if (text == "")
				{
					text += "o.SpeakForDate>=CONVERT(DATETIME,@SpeakForDateStart) AND o.SpeakForDate<=CONVERT(DATETIME,@SpeakForDateEnd)";
				}
				else
				{
					text += " AND o.SpeakForDate>=CONVERT(DATETIME,@SpeakForDateStart) AND o.SpeakForDate<=CONVERT(DATETIME,@SpeakForDateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@SpeakForDateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@SpeakForDateStart"].Value = oc.SpeakForDateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@SpeakForDateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@SpeakForDateEnd"].Value = oc.SpeakForDateEnd;
			}
			if (oc.DeliveryDateStart != null)
			{
				if (text == "")
				{
					text += "o.DeliveryDate>=CONVERT(DATETIME,@DeliveryDateStart) AND o.DeliveryDate<=CONVERT(DATETIME,@DeliveryDateEnd)";
				}
				else
				{
					text += " AND o.SpeakForDate>=CONVERT(DATETIME,@DeliveryDateStart) AND o.DeliveryDate<=CONVERT(DATETIME,@DeliveryDateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@DeliveryDateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DeliveryDateStart"].Value = oc.DeliveryDateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@DeliveryDateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DeliveryDateEnd"].Value = oc.DeliveryDateEnd;
			}
			if (oc.IsLailiao > -1)
			{
				if (text == "")
				{
					text += "o.Lailiao=@Lailiao";
				}
				else
				{
					text += " AND o.Lailiao=@Lailiao";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Lailiao", SqlDbType.Bit));
				this.selectCommand.Parameters["@Lailiao"].Value = oc.IsLailiao;
			}
			if (oc.IsQuliao > -1)
			{
				if (text == "")
				{
					text += "o.Quliao=@Quliao";
				}
				else
				{
					text += " AND o.Quliao=@Quliao";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Quliao", SqlDbType.Bit));
				this.selectCommand.Parameters["@Quliao"].Value = oc.IsQuliao;
			}
			if (oc.Locked > -1)
			{
				if (text == "")
				{
					text += "o.Locked=@Locked";
				}
				else
				{
					text += " AND o.Locked=@Locked";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Locked", SqlDbType.Bit));
				this.selectCommand.Parameters["@Locked"].Value = oc.Locked;
			}
			if (oc.Cancelled > -1)
			{
				if (text == "")
				{
					text += "o.Cancelled=@Cancelled";
				}
				else
				{
					text += " AND o.Cancelled=@Cancelled";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Cancelled", SqlDbType.Bit));
				this.selectCommand.Parameters["@Cancelled"].Value = oc.Cancelled;
			}
			if (oc.PayDateStart != null)
			{
				if (text == "")
				{
					text += "o.PayedDate>=CONVERT(DATETIME,@PayedDateStart) AND o.PayedDate<=CONVERT(DATETIME,@PayedDateEnd)";
				}
				else
				{
					text += " AND o.PayedDate>=CONVERT(DATETIME,@PayedDateStart) AND o.PayedDate<=CONVERT(DATETIME,@PayedDateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@PayedDateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@PayedDateStart"].Value = oc.PayDateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@PayedDateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@PayedDateEnd"].Value = oc.PayDateEnd;
			}
			if (oc.OrderCode != null)
			{
				if (text == "")
				{
					text += "o.Code LIKE '%'+@OrderCode+'%'";
				}
				else
				{
					text += " AND o.Code LIKE '%'+@OrderCode+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = oc.OrderCode.Replace(" ", "%");
			}
			if (oc.Remark != null)
			{
				if (text == "")
				{
					text += "o.Remark LIKE '%'+@Remark+'%'";
				}
				else
				{
					text += " AND o.Remark LIKE '%'+@Remark+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.selectCommand.Parameters["@Remark"].Value = oc.Remark.Replace(" ", "%");
			}
			if (oc.Salesperson != null)
			{
				if (text == "")
				{
					text += "o.Salesperson LIKE '%'+@Salesperson+'%'";
				}
				else
				{
					text += " AND o.Salesperson LIKE '%'+@Salesperson+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Salesperson", SqlDbType.NVarChar, 16));
				this.selectCommand.Parameters["@Salesperson"].Value = oc.Salesperson.Replace(" ", "%");
			}
			if (oc.LabelPrinted > -1)
			{
				if (text == "")
				{
					if (oc.LabelPrinted == 0)
					{
						text += "o.LabelPrintedQuantity = 0";
					}
					else
					{
						text += "o.LabelPrintedQuantity > 0";
					}
				}
				else if (oc.LabelPrinted == 0)
				{
					text += " AND o.LabelPrintedQuantity = 0";
				}
				else
				{
					text += " AND o.LabelPrintedQuantity > 0";
				}
			}
			if (text != "")
			{
				text = " WHERE " + text;
			}
			return text;
		}

		public void Dispose()
		{
			this.Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (this.dataAdapter != null)
				{
					if (this.dataAdapter.SelectCommand != null)
					{
						if (this.dataAdapter.SelectCommand.Connection != null)
						{
							this.dataAdapter.SelectCommand.Connection.Dispose();
							this.dataAdapter.SelectCommand.Connection = null;
						}
						this.dataAdapter.SelectCommand.Dispose();
						this.dataAdapter.SelectCommand = null;
					}
					this.dataAdapter.Dispose();
					this.dataAdapter = null;
				}
				if (this.insertCommand != null)
				{
					if (this.insertCommand.Connection != null)
					{
						this.insertCommand.Connection.Dispose();
						this.insertCommand.Connection = null;
					}
					this.insertCommand.Dispose();
					this.insertCommand = null;
				}
				if (this.updateCommand != null)
				{
					if (this.updateCommand.Connection != null)
					{
						this.updateCommand.Connection.Dispose();
						this.updateCommand.Connection = null;
					}
					this.updateCommand.Dispose();
					this.updateCommand = null;
				}
				if (this.deleteCommand != null)
				{
					if (this.deleteCommand.Connection != null)
					{
						this.deleteCommand.Connection.Dispose();
						this.deleteCommand.Connection = null;
					}
					this.deleteCommand.Dispose();
					this.deleteCommand = null;
				}
			}
		}
	}
}
